#include "DataCenter.h"
#include "StdSqlite3.h"
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include <openssl/sha.h>
#include "../GlobalMessage.h"


#define DBPath "./chatroom.db"
#define buffsize 1024

static SQL *s;

// 数据库的初始化，并创建用户表 修改：新增好友表的创建
bool DataCenterInit()
{
    s = StdSqlite3Init(DBPath);
    if(s == NULL)
    {
        return false;
    }
    char sql[] = "create table if not exists user(name text primary key,passwd text not null,salt text not null);";
    
    // 添加好友表
    char sqlfriend[] = "create table if not exists friends(name text ,friend text);";
    // 添加未读消息表
    char unreadmsg[] = "create table if not exists unreadmsg(flag int primary key,fromname text not null,toname not null,content text);";
    // 添加新好友申请表
    char newfriendadd[]="create table if not exists newfriendadd(fromname text ,toname text);";
    // 消息列表
    char messages[]="create table if not exists messages(sendname text,recvname text,content text,timestamp DATETIME DEFAULT CURRENT_TIMESTAMP);";
    // 群表
    char groups[] = "create table if not exists chatgroups (groupname text primary key,creator text);";
    // 群成员表
    char groupmembers[] = "create table if not exists groupmembers (username text,groupname text,quxian int);";
    // 群消息表
    char groupapplication[] = "create table if not exists groupapplication (username text,groupname text);";
    // 邀请入群表
    char groupinvite[] = "create table if not exists groupinvite (manager text,username text,groupname text);";
    
    SQLExec(s,groups);//群名
    SQLExec(s,groupmembers);//群成员和其属于的群
    SQLExec(s,groupapplication);//用户申请入群的记录  用户名 加入的群
    SQLExec(s,groupinvite);//邀请用户进入自身管理的群 管理者 用户名 群名

    return SQLExec(s, sql) && SQLExec(s, sqlfriend) && SQLExec(s, unreadmsg) && SQLExec(s, newfriendadd) && SQLExec(s, messages);
}

// 判断用户是否存在于用户表中
bool IsUserExist(const char * name)
{
    char sql[1024] = {0};
    sprintf(sql,"select count(*) from user where name = '%s';",name);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
    int count = atoi(result[1]);
    FreeTableInfo(result);
    return count != 0;
}

// 随机生成盐值
char * GenerateSalt()
{
    srand(time(NULL));
    char salt[17] = {0};
    for(int i = 0; i < 16; i++)
    {
        salt[i] = 32 + rand() % 95 ;
    }
    return strdup(salt);// 不能直接返回salt
}

// 获得哈希值
char * HashPasswd(const char * passwd, const char * salt)
{
    int newLen = strlen(passwd) + strlen(salt) + 1;
    char * newPasswd = (char *)malloc(newLen);
    if (newPasswd == NULL)
    {
        printf("AddNewUser malloc error\n");
        return NULL;
    }
    memset(newPasswd, 0, newLen);// 清空
    strcat(newPasswd, passwd);
    strcat(newPasswd, salt);

    // 哈希处理
    unsigned char hashPasswd[SHA256_DIGEST_LENGTH] = {0};
    SHA256(newPasswd, strlen(newPasswd), hashPasswd);

    // 为了好看，转换成26进制
    char result[SHA256_DIGEST_LENGTH * 2] = {0};
    for (int i = 0; i < SHA256_DIGEST_LENGTH; i++)
    {
        char temp[8] = {0};
        sprintf(temp, "%x", hashPasswd[i]);
        strcat(result, temp);
    }
    free(newPasswd);
    return strdup(result);
}

bool AddNewUser(const char * name, const char * passwd)
{
    // 生成随机盐值
    char * salt = GenerateSalt();
    char * result = HashPasswd(passwd, salt);
    char sql[1024] = {0};
    sprintf(sql,"insert into user values('%s','%s','%s');",name,passwd,salt);
    free(salt);
    free(result);
    return SQLExec(s,sql);
}

// 获得用户的盐
char * GetUserSalt(const char * name)
{
    char sql[1024] = {0};
    sprintf(sql,"select salt from user where name = '%s';",name);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return NULL;
    char * salt = strdup(result[1]);// 复制
    FreeTableInfo(result);
    return salt;
}

// 匹配账号是否相同
bool IsAccountEqual(const char * name, const char * passwd)
{
    char sql[1024] = {0};
    char * salt = GetUserSalt(name);
    if (salt == NULL)
        return false;

    char *hashpasswd = HashPasswd(passwd,salt);
    sprintf(sql,"select count(*) from user where name = '%s' and passwd = '%s';",name,passwd);
    free(hashpasswd);
    free(salt);

    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
    int count = atoi(result[1]);
    FreeTableInfo(result);
    return count != 0;
}

// 添加单向好友关系进入好友关系列表中
bool AddFriend(const char * user, const char * friend)
{
    if (IsUserExist(friend) == false)
    {
        printf("AddFriend friendname is not exist\n");
        return false;
    }
    char sql[1024] = {0};
    sprintf(sql,"insert into friends values('%s','%s');", user, friend);
    return SQLExec(s,sql);
}


// 向数据库消息表添加未读消息
bool AddUnreadMsg(MSG *m)
{
    char sql[1024] = {0};
    char contentbuffer[256] = {0};// 用于存储消息内容的临时缓冲区
    // 将消息内容从 m->Content 复制到 缓冲区
    strcpy(contentbuffer, m->Content);

    // 插入一条未读消息记录到 unreadmsg 表中
    sprintf(sql, "insert into unreadmsg values(%d, '%s', '%s', '%s');", m->flag, m->FromName, m->ToName, contentbuffer);
    return SQLExec(s, sql);
}

// 好友列表
char *FriendCollect(const char *name)
{
    char sql[1024] = {0};
    char friends[1024] = {0}; // 用于存储最终生成的好友列表字符串
    int row = 0; 
    int column = 0; 

    // 查询数据库中与指定用户相关的所有好友
    sprintf(sql,"select friend from friends where name = '%s';", name);

    char **result = GetTableInfo(s, sql, &row, &column);

    // 遍历查询结果
    for (int i = 1; i <= row; i++)
    {
        for (int j = 0; j < column; j++)
        {
            strcat(friends, result[i * column + j]); // 将好友名称追加到 friends 中
            strcat(friends, ";"); // 在好友名称后添加分号
        }
    }

    // 去掉字符串 friends 末尾多余的分号
    friends[strlen(friends) - 1] = '\0';

    FreeTableInfo(result);

    return strdup(friends);
}

// 将好友申请加入表中
bool NewFriendApplication(const char *fromname,const char *toname)
{
    char sql[1024] = {0};
    // 插入一条新的好友申请记录到 newfriendadd 表中
    sprintf(sql,"insert into newfriendadd values('%s','%s');",fromname,toname);
    return SQLExec(s,sql);
}

// 删除已查看的好友申请
bool DeleteFriendApplication(const char *fromname,const char *toname)
{
    char sql[1024] = {0};
    sprintf(sql," delete from newfriendadd where fromname = '%s' and toname = '%s';",fromname,toname);//DELETE FROM COMPANY WHERE ID = 7;
    return SQLExec(s,sql);
}

// 好友申请用户名组合成一个用分号 ; 分隔的字符串，并返回这个字符串
char *SearchFriendApplication(const char *username)
{
    int row = 0; 
    int column = 0; 
    char **result; // 用于存储查询结果
    char sql[1024] = {0}; 
    char str[1024] = {0}; // 用于存储最终生成的字符串

    // 查询所有目标为 username 的 fromname
    sprintf(sql,"select fromname from newfriendadd where toname = '%s';",username);

    // 并将结果存储到 result 中，同时获取结果的行数和列数
    result = GetTableInfo(s, sql, &row, &column);

    if (row == 0)
    {
        FreeTableInfo(result); // 释放查询结果的内存
        // 如果没有查询结果，则返回空字符串
        return strdup("");
    }
    // 遍历查询结果，将每个 fromname 追加到字符串 str 中，用 ; 隔开
    for (int i = 1; i <= row; i++)
    {
        for (int j = 0; j < column; j++)
        {
            strcat(str, result[i*column+j]); // 将 fromname 追加到 str 中
            strcat(str, ";"); // 在 fromname 后添加分号
        }
    }

    // 去掉字符串 str 末尾多余的分号
    str[strlen(str)-1] = '\0';

    // 释放查询结果的内存
    FreeTableInfo(result);

    // 返回复制的最终字符串
    return strdup(str);
}

// 判断是否是好友
bool IsFriendExist(const char *user,const char *friend)
{
    char sql[1024] = {0};
    // friends 表中是否存在 user 和 friend 的记录
    sprintf(sql,"select count(*) from friends where name = '%s' and friend = '%s';",user,friend);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
        // 查询结果的第一个数据转换为整数
    int count = atoi(result[1]);
    FreeTableInfo(result);
    // count 不等于 0，则表示是好友
    return count != 0;
}

// 删除好友
bool DeleteFriend(const char *user,const char *friend)
{
    char sql[1024] = {0};
    sprintf(sql,"delete from friends where name = '%s' and friend = '%s';",user,friend);
    return SQLExec(s,sql);
}

// 添加聊天记录
bool AddInsertMessages(MSG *m)
{
    char contentbuffer[128] = {0};// 消息 缓冲区
    strcpy(contentbuffer,m->Content);
    char sql[1024] = {0};
    sprintf(sql,"insert into messages (sendname,recvname,content) values('%s','%s','%s')",m->FromName,m->ToName,contentbuffer);
    return SQLExec(s,sql);
}



// 群管理
//判断是否已经存在群组
bool IsExistsGroup(const char *groupname)
{
    char sql[1024] = {0};
    sprintf(sql,"select count(*) from chatgroups where groupname = '%s';",groupname);// 查询指定群组名称的记录数量
    char **result = GetTableInfo(s,sql,NULL,NULL);// 获取结果存储在result数组中
    if(result == NULL)
        return false;
    int count = atoi(result[1]);// 将结果的第二个元素转换为整数
    FreeTableInfo(result);
    return count != 0;// 如果count不等于0，说明群组存在
}


//根据名称创建一个组名
bool ServerCreateGroup(const char *groupname,const char *creator)
{
    char sql[1024] = {0};
    sprintf(sql,"insert into chatgroups values ('%s','%s');",groupname,creator);
    return SQLExec(s,sql);
}

//添加用户进入群成员关系表中
bool InserintoGroupMembers(const char *groupname,const char *username,int quanxian)
{
    char sql[1024] = {0};
    //将群组名称和创建者信息插入到chatgroups表中
    sprintf(sql,"insert into groupmembers values ('%s','%s',%d);",username,groupname,quanxian);
    return SQLExec(s,sql);
}

//判断该用户是否已经在指定的群中
bool IsExistGroupMember(const char *groupname,const char *username)
{
    char sql[1024] = {0};
    sprintf(sql,"select count(*) from groupmembers where groupname = '%s' and username = '%s';",groupname,username);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
    int count = atoi(result[1]);
    FreeTableInfo(result);
    return count != 0;
}

//有人申请入群，将其请求添加进groupapplication表中
bool InsertGroupApplication(const char *groupname,const char *username)
{
    char sql[1024] = {0};
    sprintf(sql,"insert into groupapplication values ('%s','%s');",username,groupname);
    return SQLExec(s,sql);
}

//判断申请入群表中是否存在特定信息
bool IsExistGroupApplication(const char *username,const char *groupname)
{
    char sql[1024] = {0};
    // 查询指定用户名和群组名的申请记录数
    sprintf(sql,"select count(*) from groupapplication where groupname = '%s' and username = '%s';",groupname,username);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
    int count = atoi(result[1]);
    FreeTableInfo(result);
    return count != 0;// 返回记录数是否不为0，表示是否存在该申请记录
}

//审批入群通知，将其从表中删除
bool DeleteGroupApplication(const char *groupname,const char *username)
{
    char sql[1024] = {0};
    sprintf(sql,"delete from groupapplication where username = '%s' and groupname = '%s';",username,groupname);
    return SQLExec(s,sql);
}

//将群邀请添加到表中
bool InsertGroupInvite(const char *manger,const char *username,const char *groupname)
{
    //char groupinvite[] = "create table if not exists groupinvite (manager text,username text,groupname text);";
    char sql[1024] = {0};
    sprintf(sql,"insert into groupinvite values ('%s','%s','%s');",manger,username,groupname);
    return SQLExec(s,sql);
}

//审批群邀请，将其从表中删除
bool DeleteGroupInvite(const char *username,const char *groupname)
{
    char sql[1024] = {0};
    sprintf(sql,"delete from groupinvite where  username = '%s' and groupname = '%s' ;",username,groupname);
    return SQLExec(s,sql);
}

//查看群是否有username管理
bool IsManageGroup(const char *username,const char *groupname)
{
    char sql[1024] = {0};
    sprintf(sql,"select count(*) from chatgroups where groupname = '%s' and creator = '%s';",groupname,username);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
    int count = atoi(result[1]);
    FreeTableInfo(result);
    return count != 0;
}

//根据用户名查找该用户管理的群的数量
int CountGroupManger(const char *username)
{
    char sql[1024] = {0};
    sprintf(sql,"select count(*) from chatgroups where  creator = '%s';",username);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
    int count = atoi(result[1]);
    FreeTableInfo(result);
    return count ;
}

//根据用户名查找该用户管理的所有的群的名称
char *SearchMangerName(const char *username)
{
    char sql[buffsize] = {0};
    sprintf(sql,"select groupname from chatgroups where creator = '%s';",username);
    int row = 0;
    int column = 0;
    char **result = GetTableInfo(s,sql,&row,&column);// 获取结果表
    if(result == NULL)
        return NULL;// 表示该用户没有管理的群组
    char groups[buffsize] = {0};// 用于存储查询结果的群组名称
    for (int i = 1; i <= row; i++)// 从第一行
    {
        for (int j = 0; j < column; j++)// 遍历每一列
        {
            strcat(groups,result[i*column + j]);//将当前单元格的值追加到groups数组中
        }
        strcat(groups,",");// 在每个群组名称后面添加逗号分隔符
    }
    groups[strlen(groups) - 1] = '\0';// 将最后一个逗号替换为字符串结束符
    FreeTableInfo(result); // 释放查询结果占用的内存
    return strdup(groups); // 返回群组名称字符串的副本
}

//根据组名找所有申请加入该群组的用户 用，分隔
char *SearchGroupApplication(const char *groups)
{
    char sql[buffsize] = {0};
    char usernames[buffsize] = {0};// 用于存储用户名列表
    sprintf(sql,"select username from groupapplication where groupname = '%s';",groups);
    int row = 0;
    int column = 0;
    char **result = GetTableInfo(s,sql,&row,&column);
    if(result == NULL)
        return NULL;
    for (int i = 1; i <= row; i++)
    {
        for (int j = 0; j < column; j++)
        {
            strcat(usernames,result[i*column + j]);// 将当前单元格的值追加到usernames数组中
        }
        strcat(usernames,",");// 添加逗号分隔符
    }
    usernames[strlen(usernames) - 1] = '\0';// 将最后一个逗号替换为字符串结束符
    FreeTableInfo(result);// 释放查询结果占用的内存
    return strdup(usernames);//// 返回用户字符串的副本
}

//根据用户名展示所有的邀请入群信息 谁 邀请加入 群名 manager，groupname；
char *SearchGroupInvite(const char *username)
{
    char sql[buffsize] = {0};
    char invition[buffsize] = {0};// 用于存储邀请信息
    sprintf(sql,"select manager, groupname from groupinvite where username = '%s';",username);
    int row = 0;
    int column = 0;
    char **result = GetTableInfo(s,sql,&row,&column);
    if(result == NULL)
        return NULL;// 返回NULL，表示没有邀请信息
    for (int i = 1; i <= row; i++)
    {
        strcat(invition,result[i*column]);//将当前行的第一列（管理者）追加到invition数组中
        strcat(invition,",");
        strcat(invition,result[i*column+1]);// 将当前行的第二列（群名）追加到invition数组中
        strcat(invition,";");// 添加分号分隔符
    }
    invition[strlen(invition) - 1] = '\0';// 将最后一个分号替换为字符串结束符
    FreeTableInfo(result);// 释放查询结果占用的内存
    return strdup(invition);// 返回邀请信息字符串的副本
}

//查看邀请表中是否存在特定邀请
bool IsExistGroupInvition(const char*groupname,const char *username)
{
    char sql[buffsize] = {0};
    sprintf(sql,"select count(*) from groupinvite where groupname = '%s' and username = '%s';",groupname,username);
    char **result = GetTableInfo(s,sql,NULL,NULL);
    if(result == NULL)
        return false;
    int count = atoi(result[1]);
    FreeTableInfo(result);
    return count != 0;
}

//查找所有的群
char *SearchGroups()
{
    char str[buffsize] = {0};//存储查询结果的连接字符串
    char *sql = "select groupname from chatgroups";
    int row = 0;
    int column = 0;
    char **result = GetTableInfo(s,sql,&row,&column);
    if(result == NULL)
        return NULL;
    for (int i = 1; i <= row; i++)
    {
        for (int j = 0; j < column; j++)
        {
            strcat(str,result[i*column+j]);// 将当前单元格的内容追加到str数组中
        }
        strcat(str,",");
    }
    str[strlen(str) -1] = '\0';
    FreeTableInfo(result); // 释放查询结果占用的内存
    return strdup(str);

}

// 删除群
bool DeleteGroup(const char *groupname,const char *username)
{
    char sql[1024] = {0};
    sprintf(sql," delete from chatgroups where name = '%s' and friend = '%s';",groupname,username);
    return SQLExec(s,sql);
}

// 添加聊天记录
bool AddChatHistory(const char *fromname, const char *toname, const char *content)
{
    char sql[1024] = {0};
    sprintf(sql, "insert into chathistory (fromname, toname, content) values('%s','%s','%s');", fromname, toname, content);
    return SQLExec(s, sql);
}

char *GetChatHiostory(const char *fromname, const char *toname)
{
    char sql[1024] = {0};
    int row = 0;
    int colnm = 0;
    sprintf(sql, "SELECT fromname, time, content FROM chathistory where (fromname = '%s' and toname = '%s') or (fromname = '%s' and toname = '%s') ORDER BY time ASC;", fromname, toname, toname, fromname); // 按时间升序
    char **result = GetTableInfo(s, sql, &row, &colnm);
    char ret[ContentSize] = { 0 };
    int i = 3;
    while (i < ((row + 1) * colnm))
    {
        strcat(ret, result[i++]);
        strcat(ret, ",");
    }
    strcat(ret, "\0");
    FreeTableInfo(result);
    return strdup(ret);
}